package week7.day46;

import com.mysql.cj.jdbc.Driver;

import java.sql.*;

/**
 *
 * 某大学研究生院有若干研究生导师，包括职工编号、姓名、职称、研究方向，其中每个导师的职工编号是唯一的。
 * 若干研究生，包括学号、姓名、性别、入学日期，其中每个研究生的学号是唯一的。
 * 每个导师可以带若干研究生，但每个研究生只能有一个导师。
 * 请设计一个数据库，要求可以正确体现导师和研究生之间的关系。
 * 设计完毕之后，请插入一定量的数据，并验证你设计的数据库是否满足要求。
 * 在你设计的表中插入若干数据。
 * 1.请查出每个导师所带研究生的姓名。
 * 2.清查出特定姓名的导师所带研究生的姓名。
 * 3.请查出每个导师所带研究生的数量。
 * 4.请查出每个导师所带的男研究生的数量。
 * 5.请找出选择哪个研究方向的导师最多。
 * 6.请找统计不同职称的导师的个数。
 * @Author fengzihao
 * @Date 2024-05-30 18:39
 */
public class JdbcHomeWork {
    public static void main(String[] args) {
        try {
            DriverManager.registerDriver(new Driver());
            String url = "jdbc:mysql://localhost:3306/60th?useSSL=false&characterEncoding=utf8";
            String username = "root";
            String password = "123456";
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            int createPostgraduateTutorUpdateRow = statement.executeUpdate("create table postgraduate_tutor(id int primary key auto_increment,name varchar(30),professional_title varchar(40),research_direction varchar(100))");
            int createPostgraduateUpdateRow = statement.executeUpdate("create table postgraduate(id int primary key auto_increment,name varchar(30),gender varchar(10),enrollment_date date,tutor_id int,foreign key (tutor_id) references postgraduate_tutor(id))");
            int insertPostgraduateTutorUpdateRow = statement.executeUpdate("insert into postgraduate_tutor(name,professional_title,research_direction) values('张三','教授','生物信息学'),('李四','副教授','生物信息学'),('王五','副教授','生物信息学'),('赵六','讲师','生物信息学'),('孙七','讲师','生物信息学')");
            int insertPostgraduateUpdateRow = statement.executeUpdate("insert into postgraduate(name,gender,enrollment_date,tutor_id) values ('冯子浩','男','2019-09-01',1),('秦潞','女','2019-09-01',2),('彭轶芬','女','2019-09-01',3),('曹晓宇','男','2023-09-01',4),('王成','男','2018-09-01',5),('陈晨','女','2022-09-01',2),('张朋','男','2017-09-01',1),('段家飞','男','2020-09-01',5)");
            // 1.请查出每个导师所带研究生的姓名。
            ResultSet resultSet1 = statement.executeQuery("select t.name,group_concat(p.name) from postgraduate p,postgraduate_tutor t where p.tutor_id = t.id group by t.name");
            while (resultSet1.next()) {
                System.out.println(resultSet1.getString(1)+" "+resultSet1.getString(2));
            }
            System.out.println("=====================================");
            // 2.清查出特定姓名的导师所带研究生的姓名。
            ResultSet resultSet2 = statement.executeQuery("select t.name,group_concat(p.name) from postgraduate p,postgraduate_tutor t where p.tutor_id = t.id and t.name = '张三' group by t.name");
            while (resultSet2.next()) {
                System.out.println(resultSet2.getString(1)+" "+resultSet2.getString(2));
            }
            System.out.println("=====================================");
            // 3.请查出每个导师所带研究生的数量。
            ResultSet resultSet3 = statement.executeQuery("select t.name,count(*) from postgraduate p,postgraduate_tutor t where p.tutor_id = t.id group by t.name");
            while (resultSet3.next()) {
                System.out.println(resultSet3.getString(1)+" "+resultSet3.getString(2));
            }
            System.out.println("=====================================");
            // 4.请查出每个导师所带的男研究生的数量。
            ResultSet resultSet4 = statement.executeQuery("select t.name,count(p.name) from postgraduate p,postgraduate_tutor t where p.tutor_id = t.id and p.gender = '男' group by t.name");
            while (resultSet4.next()) {
                System.out.println(resultSet4.getString(1)+" "+resultSet4.getString(2));
            }
            System.out.println("=====================================");
            // 5.请找出选择哪个研究方向的导师最多。
            ResultSet resultSet5 = statement.executeQuery("select research_direction,count(research_direction) from postgraduate_tutor group by research_direction order by count(research_direction) desc limit 1");
            while (resultSet5.next()) {
                System.out.println(resultSet5.getString(1)+" "+resultSet5.getString(2));
            }
            System.out.println("=====================================");
            // 6.请找统计不同职称的导师的个数。
            ResultSet resultSet6 = statement.executeQuery("select professional_title,count(professional_title) from postgraduate_tutor group by professional_title");
            while (resultSet6.next()) {
                System.out.println(resultSet6.getString(1)+" "+resultSet6.getString(2));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
